﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QQTools.Data.Models;
using QQTools.Data.Enums.BlockGroup;
using QQTools.Logging;


namespace QQTools.Data.Repositories.BlockGroup
{
    public class BlockGroupRepository
    {
        private readonly string _connectionString;

        public BlockGroupRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// 新增数据（异步）
        /// </summary>
        public async Task AddAsync(BlockGroupModel blockGroup)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand(
                        "INSERT INTO block_groups (qq, group_number, status, create_time, update_time) " +
                        "VALUES (@QQ, @GroupNumber, @Status, @CreateTime, @UpdateTime)", connection);
                    command.Parameters.AddWithValue("@QQ", blockGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", blockGroup.GroupNumber);
                    command.Parameters.AddWithValue("@Status", blockGroup.Status);
                    command.Parameters.AddWithValue("@CreateTime", blockGroup.CreateTime);
                    command.Parameters.AddWithValue("@UpdateTime", blockGroup.UpdateTime);

                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("新增数据失败", ex);
            }
        }

        /// <summary>
        /// 删除数据（异步）
        /// </summary>
        public async Task DeleteAsync(int id)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand("DELETE FROM block_groups WHERE id = @Id", connection);
                    command.Parameters.AddWithValue("@Id", id);

                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("删除数据失败", ex);
            }
        }

        /// <summary>
        /// 根据 ID 查询数据（异步）
        /// </summary>
        public async Task<BlockGroupModel> GetByIdAsync(int id)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand("SELECT * FROM block_groups WHERE id = @Id ORDER BY id ASC", connection);
                    command.Parameters.AddWithValue("@Id", id);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return MapToBlockGroup(reader);
                        }
                    }
                }
                return null;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("查询数据失败", ex);
            }
        }

        /// <summary>
        /// 查询所有数据（异步）
        /// </summary>
        public async Task<List<BlockGroupModel>> GetAllAsync()
        {
            var blockGroups = new List<BlockGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM block_groups ORDER BY id ASC", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            blockGroups.Add(new BlockGroupModel
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("id")),
                                QQ = reader.GetString(reader.GetOrdinal("qq")),
                                GroupNumber = reader.GetString(reader.GetOrdinal("group_number")),
                                Status = (BlockGroupStatus)reader.GetInt32(reader.GetOrdinal("status")),
                                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
                            });
                        }
                    }
                }
            }

            return blockGroups;
        }

        /// <summary>
        /// 清空表（异步）
        /// </summary>
        public async Task ClearTableAsync()
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new SQLiteCommand("DELETE FROM block_groups;", connection))
                    {
                        await command.ExecuteNonQueryAsync();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("清空表失败", ex);
            }
        }

        /// <summary>
        /// 根据群号和 QQ 修改状态（异步）
        /// </summary>
        public async Task UpdateStatusByQQAndGroupNumberAsync(string qq, string groupNumber, BlockGroupStatus status)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand(
                        "UPDATE block_groups SET status = @Status, update_time = @UpdateTime " +
                        "WHERE qq = @QQ AND group_number = @GroupNumber", connection);
                    command.Parameters.AddWithValue("@Status", (int)status);
                    command.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);

                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("更新状态失败", ex);
            }
        }

        /// <summary>
        /// 将所有状态为进行中(BlockGroupStatus.Bebeing)的重置为待处理（异步）
        /// </summary>
        public async Task ResetStatusToPendingAsync()
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand(
                        "UPDATE block_groups SET status = 1, update_time = @UpdateTime WHERE status = @Status", connection);
                    command.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                    command.Parameters.AddWithValue("@Status", (int)BlockGroupStatus.Bebeing);

                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("重置状态失败", ex);
            }
        }



        /// <summary>
        /// 获取下一个需要处理的记录，并锁定该记录
        /// </summary>
        /// <returns></returns>
        public async Task<BlockGroupModel> GetAndLockNextBlockGroupAsync()
        {
            BlockGroupModel blockGroup = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            // 查询第一条待处理数据
                            var selectCommand = new SQLiteCommand(
                                "SELECT TOP 1 * FROM block_groups WITH (UPDLOCK, ROWLOCK) WHERE status = 1 ORDER BY id ASC",
                                connection, transaction);
                            using (var reader = await selectCommand.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    blockGroup = MapToBlockGroup(reader);
                                }
                            }

                            if (blockGroup != null)
                            {
                                // 更新状态为进行中
                                var updateCommand = new SQLiteCommand(
                                    "UPDATE block_groups SET status = 2, update_time = @UpdateTime WHERE id = @Id",
                                    connection, transaction);
                                updateCommand.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                                updateCommand.Parameters.AddWithValue("@Id", blockGroup.Id);

                                await updateCommand.ExecuteNonQueryAsync();
                            }

                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            Console.WriteLine($"Transaction failed: {ex.Message}");
                        }
                    }
                }
                return blockGroup;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("取出并更新状态失败", ex);
            }
        }

        /// <summary>
        /// 根据 QQ 查询所有数据（异步）
        /// </summary>
        public async Task<List<BlockGroupModel>> GetByQQAsync(string qq)
        {
            var blockGroups = new List<BlockGroupModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand(
                        "SELECT * FROM block_groups WHERE qq = @QQ ORDER BY id ASC", connection);
                    command.Parameters.AddWithValue("@QQ", qq);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            blockGroups.Add(MapToBlockGroup(reader));
                        }
                    }
                }
                return blockGroups;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("根据 QQ 查询数据失败", ex);
            }
        }

        /// <summary>
        /// 映射 System.Data.Common.DbDataReader 到 BlockGroup 对象
        /// </summary>
        private BlockGroupModel MapToBlockGroup(System.Data.Common.DbDataReader reader)
        {
            return new BlockGroupModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.GetString(reader.GetOrdinal("qq")),
                GroupNumber = reader.GetString(reader.GetOrdinal("group_number")),
                Status = (BlockGroupStatus)reader.GetInt32(reader.GetOrdinal("status")),
                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }

        public async Task UpdateAsync(BlockGroupModel blockGroup, bool updateStatus = true)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                string updateCommandText = @"
                    UPDATE block_groups
                    SET qq = @QQ, group_number = @GroupNumber, 
                        update_time = CURRENT_TIMESTAMP";
                if (updateStatus)
                {
                    updateCommandText += ", status = @Status";
                }
                updateCommandText += " WHERE id = @Id;";

                using (var command = new SQLiteCommand(updateCommandText, connection))
                {
                    command.Parameters.AddWithValue("@Id", blockGroup.Id);
                    command.Parameters.AddWithValue("@QQ", blockGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", blockGroup.GroupNumber);
                    if (updateStatus)
                    {
                        command.Parameters.AddWithValue("@Status", blockGroup.Status);
                    }

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 如果 qq 和 group_number 都存在则更新数据，否则插入新数据
        /// </summary>
        /// <param name="blockGroup"></param>
        /// <returns></returns>
        public async Task AddOrUpdateAsync(BlockGroupModel blockGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                // 检查 qq 和 group_number 是否已经存在
                using (var checkCommand = new SQLiteCommand(@"
                        SELECT id FROM block_groups
                        WHERE qq = @QQ
                        AND group_number = @GroupNumber 
                        ORDER BY id ASC", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", blockGroup.QQ);
                    checkCommand.Parameters.AddWithValue("@GroupNumber", blockGroup.GroupNumber);
                    var existingId = await checkCommand.ExecuteScalarAsync();

                    if (existingId != null)
                    {
                        // 如果存在，则更新数据, 但是不更新 status
                        int id = Convert.ToInt32(existingId);
                        blockGroup.Id = id;
                        await UpdateAsync(blockGroup, updateStatus: false);
                    }
                    else
                    {
                        // 如果不存在，则插入新数据
                        await AddAsync(blockGroup);
                    }
                }
            }
        }


        /// <summary>
        /// 根据 qq 和 status 查询记录数量
        /// </summary>
        /// <param name="qq"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public async Task<int> GetCountByQQAndStatusAsync(string qq, BlockGroupStatus status)
        {
            int count = 0;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM block_groups
                    WHERE qq = @QQ
                    AND status = @Status;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@Status", (int)status);

                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }

            return count;
        }


        /// <summary>
        /// 根据QQ获取下一个需要处理的记录，并锁定该记录
        /// </summary>
        /// <param name="qq">QQ</param>
        /// <returns></returns>
        public async Task<BlockGroupModel> GetAndLockNextBlockGroupAsync(string qq)
        {
            BlockGroupModel blockGroup = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 获取 status 为 1 且 qq 匹配的记录，并按 id 升序排列
                        using (var command = new SQLiteCommand(@"
                            SELECT * FROM block_groups
                            WHERE status = 1 AND qq = @QQ
                            ORDER BY id ASC
                            LIMIT 1;", connection, transaction))
                        {
                            command.Parameters.AddWithValue("@QQ", qq);
                            using (var reader = await command.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    blockGroup = MapToBlockGroup(reader);
                                }
                            }
                        }

                        if (blockGroup != null)
                        {
                            // 更新 status 为 3
                            using (var updateCommand = new SQLiteCommand(@"
                                UPDATE block_groups
                                SET status = 3, update_time = CURRENT_TIMESTAMP
                                WHERE id = @Id;", connection, transaction))
                            {
                                updateCommand.Parameters.AddWithValue("@Id", blockGroup.Id);
                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"Transaction failed: {ex.Message}");
                    }
                }
            }

            return blockGroup;
        }


        /// <summary>
        /// 根据QQ获取记录总数
        /// </summary>
        /// <param name="qq"></param>
        /// <returns></returns>
        public async Task<int> GetCountByQQAsync(string qq)
        {
            int count = 0;
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM block_groups
                    WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }
            return count;
        }

        /// <summary>
        /// 根据QQ获取status为BlockGroupStatus.Success的总记录数
        /// </summary>
        /// <param name="qq"></param>
        public async Task<int> GetSuccessCountByQQAsync(string qq)
        {
            int count = 0;
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM block_groups
                    WHERE qq = @QQ
                    AND status = @Status;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@Status", (int)BlockGroupStatus.Success);
                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }
            return count;
        }

        /// <summary>
        /// 批量插入记录：如果记录已存在（基于 qq 和 group_number），则跳过
        /// </summary>
        /// <param name="models">待插入的 BlockGroupModel 集合</param>
        public async Task BulkInsertAsync(List<BlockGroupModel> models)
        {
            if (models == null || models.Count == 0)
            {
                LogManager.Info("BulkInsertAsync：传入的记录集合为空，无需插入。");
                return;
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        foreach (var model in models)
                        {
                            // 使用 INSERT ... SELECT 结合 WHERE NOT EXISTS 判断记录是否存在
                            string sql = @"
                                INSERT INTO block_groups 
                                (qq, group_number, status, create_time, update_time)
                                SELECT @qq, @group_number, @status, @create_time, @update_time
                                WHERE NOT EXISTS (
                                    SELECT 1 FROM block_groups WHERE qq = @qq AND group_number = @group_number
                             );";

                            using (var command = new SQLiteCommand(sql, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@qq", model.QQ);
                                command.Parameters.AddWithValue("@group_number", model.GroupNumber);
                                command.Parameters.AddWithValue("@status", (int)model.Status);
                                command.Parameters.AddWithValue("@create_time", model.CreateTime);
                                command.Parameters.AddWithValue("@update_time", model.UpdateTime);

                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info("BulkInsertAsync 成功批量插入记录（存在重复记录时跳过）。");
            }
            catch (Exception ex)
            {
                LogManager.Error("BulkInsertAsync 异常：" + ex);

            }
        }
    }
}
